eDocs Home > BEA AquaLogic Data Services Platform Documentation > Data Services Developer's Guide > Contents

How To Create Physical Data Services from Stored Procedures

Stored procedures are database objects that group an executable set of SQL and native database programming language statements together to perform a specific task locally. Advanced DBMS systems utilize stored procedures to improve query performance, manage and schedule data operations, enhance security, and so forth.

In ALDSP you can, for specifically supported databases, create physical data services based on stored procedures.

It is often convenient to leverage independent routines as part of managing enterprise information through a data service. An obvious example would be to leverage standalone update or security functions through data services. Such functions have no XML type; in fact, they typically return nothing (or void).

Stored procedures are very often side-effecting from the perspective of the data service, since they perform internal operations on data. In such cases all you need to do is identify the stored procedures as a data service procedure when your physical data service is created.

After you have identified the stored procedures that you want to add to your data service, you also have an opportunity to identify which of these should be identified as data service procedures.

Each stored procedure that is imported becomes a separate data service. In other words, if you have five stored procedures, you will create five data services.

The following topics describe how to create a physicald data service from a stored procedure:

References Configuration Information Terms Commonly Used When Discussing Stored Procedures For details on creating and managing stored procedures in your database, see the documentation provided with the DBMS.

Importing Stored Procedure Metadata Using the Physical Data Service Creation Wizard

The following topics cover the actions necessary to create physical data services from relational stored procedures.

Setting Up the Physical Data Service Creation Wizard

Physical data services are created using a wizard.

Physical Data Service Creation Wizard

Starting the Wizard

To start the physical data service creation wizard:

1. Right-click on your dataspace project or any folder in your project.
2. Choose New > Physical Data Service

Creating a New Physical Data Service

Setting Up the Import Wizard for Relational Objects

When importing a relational object available options include the ability to:

1. Set a location for your new data service to be saved within your project.
2. Select a data source from the dropdown listbox.
3. Select the database type for the selected source (PointBase for the sample RDBMS) from the dropdown listbox.
4. Select among the relational source types listed in the following table. 

Types of available relational data sources

Selecting Stored Procedure Objects for Import

To create physical data services based on stored procedures:

1. Select the Stored Procedures option.
2. Click Next.  

A list of available stored procedures appears.

Objects are grouped based on the relational data sources catalog and/or schema.

You can use wildcards to support importing metadata on internal stored procedures. For example, entering the following string as a stored procedure filter:

%TRIM%

STANDARD.TRIM

Database-specific Catalog and Schema Considerations

Simply check the desired objects or their container, which will select all enclosed stored procedures.

Stored Procedure Objects Selected for Import

Filtering SQL Objects Using Search

The Search option available when creating a physical data service can be especially useful when:

• You know specific names of the data source objects you want to turn into data services.
• Your data source may be so large that a filter is needed.
• You may be looking for objects with specific naming characteristics such as:

%audit2003%

The above search command retrieves all objects that contain the enclosed string.

Using JDBC Syntax to Search SQL Objects

You can search through available SQL objects using standard JDBC wildcard syntax.

• An underscore (_) creates a wildcard for an individual character.
• A percentage sign (%) indicates a wildcard for a string. Entries are case-sensitive.

Another example:

CUST%, PAY%

entered in the Tables/Views field the above search string returns all tables and views starting with either CUST or PAY.

Special Considerations When Searching Stored Procedures 

If no items are entered for a particular field, all matching items are retrieved. For example, if no filtering entry is made for the Procedure field, all stored procedures in the data object will be retrieved.

Configuring Selected Stored Procedures

When ALDSP introspects a stored procedure, the process may not be complete. For example, a required item of information such as a schema file or type cannot be determined. When such introspection problems occur, the stored procedure in question is highlighted in red. This setting means that additional information about the procedure must be provided by the user before the data service can be created.

Your goal in correcting an "<unknown>" condition associated with a stored procedure is to bring the metadata obtained by the import wizard into conformance with the actual metadata of the stored procedure. In some cases this will be by correcting the location of the return type. In others you will need to adjust the type associated with an element of the procedure or add elements that were not found during the initial introspection of the stored procedure.

Configure Stored Procedure Dialog

When several stored procedures are selected at the same time for physical data service creation, all the selected procedures must be adequately configured before any data services based on the procedures can be created.

An alternative to configuring a incomplete stored procedure before proceeding is to use the wizard Back button to de-select the procedure in question.

Here are the steps involved in editing a set of stored procedures that will be imported as data services:

1. Scroll through the list of selected procedures.
2. For each procedure in red type, use the Edit button to correct the configuration settings.
3. Make any other changes. (In some cases the data architect may know of requirements that are not identified during the introspection process.)
4. Click Next when all the procedures in the selected set are valid.

   If a stored procedure has only one return value and the value is either simple type or a RowSet which is mapping to an existing schema, no schema file is created. This stored procedure by definition become a library data service.

Editing Stored Procedure Configurations

Stored procedure configuration can be complicated. An understanding of the characteristics of the stored procedure in your database is an essential prerequisite. This section describes stored procedure options in detail.  

Stored Procedure Metadata Editing Options

Once in stored procedure configuration edit mode, options are available in three general areas:

• Parameters. Stored procedures requiring complex parameters can only be turned into data services once a schema has been identified. In addition, retrieved information on parameters required by a stored procedure may be incorrect. For example, additional parameters may be needed.
• Return  type. Stored procedures returning complex data require a local schema to handle data returned from the call. In addition, retrieved information on stored procedure return types may be incorrect or it may be the case that no returned data is wanted.
• Row set. A row set identifies a schema and its associated library data service to hold information returned by a stored procedure. In some cases multiple row sets may need to be specified.

Stored Procedure Editing Options

Stored Procedure Configuration Reference

The following topics provide detailed information regarding various configuration options associated with creating data services based on stored procedures. 

In Mode, Out Mode, Inout Mode

In, Out, and Inout mode settings determine how a parameter passed to a stored procedure is handled.

Procedure Profile

Each element in a stored procedure is associated with a type. If the item is a simple type, you can simply choose from the pop-up list of types. If the type is complex, you may need to supply an appropriate schema. Click on the schema location button and either enter a schema pathname or browse to a schema. The schema must reside in your application.

After selecting a schema, both the path to the schema file and the URI appear. 

Complex Parameter Types

Complex parameter types are supported under only three conditions:

• As the output parameter
• As the Return type
• As a rowset

About Rowsets

A rowset type is a complex type.

The rowset type contains a sequence of a repeatable elements (for example called CUSTOMER) with the fields of the rowset.

In some cases the wizard can automatically detect the structure of a rowset and create an element structure. However, if the structure is unknown, you will need to provide it. 

All rowset-type definitions must conform to this structure.

The name of the rowset type can be:

• The parameter name (in case of a input/output or output only parameter).
• An assigned name.
• The referenced element name (result rowsets) in a user-specified schema.

Not all databases support rowsets. In addition, JDBC does not report information related to defined rowsets.

Using Rowset Information 

In order to create data services from stored procedures that use rowset information, you need to supply the correct ordinal (matching number) and a schema. If the schema has multiple global elements, select the one you want from the Type column. Otherwise the type used match the first global element in your schema file.

The order of rowset information is significant; it must match the order in your data source. Use the Move Up / Move Down commands to adjust the ordinal number assigned to the rowset.

XML types in data services generated from stored procedures do not display native types. However, you can view the native type in the Source editor; it is located in the pragma section.

Stored Procedure Version Support

Only the most recent version of a particular stored procedure can be imported into ALDSP. For this reason you cannot identify a stored procedure version number when creating a physical data service based on a stored procedure. Similarly, adding a version number for your stored procedure in the Source editor will result in a query exception.

Supporting Stored Procedures with Nullable Input Parameter(s)

If you know that an input parameter of a stored procedure is nullable (can accept null values), you can change the signature of the function in Source View to make such parameters optional by adding a question mark at end of the parameter.

For example (question-mark (?) shown in bold):

function myProc($arg1 as xs:string) ...

would become:

function myProc($arg1 as xs:string?) ...

Setting Properties for New Data Service Operations

Each new entity data service is created with a Read function that contains all the metadata elements identified during data service creation. It can be thought of as comparable to the following construct in the relational world:

select * from <table>

Use the Properties dialog to:

• Optionally modify the operation name.

• Set the Public option (check if you want your function to be available to client applications).
• Set the kind of operation (in some cases only Read will be available).
• Set the Primary option (check if you want your function to be the primary of its type).

  In some cases this option may not be available.

• Select a common XML namespace for the entire data service or individual target namespaces for specific operations.
• Set the target namespace.

The root element, which is read-only, is also displayed.

Initially the root element name matches the name of the data service.

Setting Properties for New Data Service Functions

Default Naming Conventions 

There are several default naming conventions associated with new data services:

• When a table, view, or other data source object is the source for a data service, the nominated name is wherever possible the same as the source object name. In some cases, however, names are adjusted to conform with XML naming conventions.

• Initially the root element name matches the name of the data service.

  XML Name Conversion Considerations

Verifying Data Service Composition

On the Review New Data Service(s) page you can set, confirm or, optionally, change suggested data service names depending on the type of physical data service you are creating.

Default Physical Data Service Names 

The nominated name for a new data service is, wherever possible, the same as the source object name. In some cases, however, names are adjusted to conform with XML naming conventions.

XML Name Conversion Considerations

About Automatic Data Service Name Changes

Name conflicts occur when there is a data service of the same name present in the target directory. Name conflicts are highlighted in red.

There are several situations where you will need to change the name of your data service:

• There already is a data service of the same name in your application.
• You are trying to create multiple data services with the same name.

Data services always have the file extension:

.ds

Adding Operations to an Existing Data Service

You can add SQL statement or stored procedure operations based on the same data source to an existing physical data service based a stored procedure.

Add an External Function to an Existing Physical Data Service

Adding a Stored Procedure or SQL Statement to a Data Service 

Support for Stored Procedures in Popular Databases

Each database vendor approaches stored procedures differently. ALDSP support limitations generally reflect JDBC driver limitations.

General Restrictions

There are several restrictions that apply to stored procedures generally: 

• ALDSP does not support rowset as an input parameter.
• Only data types supported by ALDSP can be imported as part of stored procedures.

  For a list of database types supported by ALDSP XQuery-SQL Mapping Reference

Oracle Stored Procedure Support

The following table describes data service creation support for Oracle stored procedures. 

Sybase Stored Procedure Support

The following table describes data service creation support for Sybase stored procedures. 

IBM DB2 Stored Procedure Support

The following table describes data service creation support for IBM DB2 stored procedures.

Microsoft SQL Server Stored Procedure Support

The following table describes data service creation support for Microsoft stored procedures.